package vivek.project.pocketmoney.expenses.scripts
{
	import flash.data.*;
	import flash.errors.*;
	
	import vivek.project.pocketmoney.admin.model.AdminDataCache;

	public class ExpenseSQLScript
	{
		public static const ACCOUNT_SUMMARY:String="select  am_accountname,em_accountid ,am_accountname,em_expensetype ,round(sum(em_amount),2) amount,am_accounttype, at_desc from expensemanager,accountmanager, accounttype where em_accountid=am_accountid  and at_id=am_accounttype and am_userid in (select userid from selecteduser) group by em_accountid,em_expensetype";
		public static const ADD_CATEGORY:String="insert into categorymanager (cm_categoryname,cm_categorydesc,cm_userid,cm_expensetype) values (:cm_categoryname,:cm_categorydesc,:USERID,:cm_expensetype)";
		public static const ADD_PAYEE:String="insert into payeemanager (pm_payeeName,pm_userid) values (:pm_payeeName,:USERID)";
		public static const ADD_RECURSSIVE_EXPENSE:String="INSERT INTO  recurringexpensemanager (rm_recursiondate ,rm_amount, rm_fromaccount ,rm_toaccount ,rm_fromdate ,rm_todate ,rm_type , rm_category ,rm_memo,rm_status,rm_nextexecutiondate,rm_last_executed_date,rm_userid ) " + "VALUES (:rm_recursiondate ,:rm_amount, :rm_fromaccount ,:rm_toaccount ,:rm_fromdate ,:rm_todate ,:rm_type , :rm_category ,:rm_memo,:rm_status,:rm_nextexecutiondate,:rm_fromdate,:USERID)";
		public static const DELETE_CATEGORY_RECORD:String="delete from categorymanager where cm_categoryid = :cm_categoryid and cm_categorytype != 1";
		public static const DELETE_EXPENSE_RECORD:String="delete from expensemanager where em_expenseid = :expenseId";
		public static const DELETE_PAYEE:String="DELETE FROM PAYEEMANAGER WHERE PM_ID = :pm_payeeid";
		public static const DELETE_RECURRING_EXPENSE:String="DELETE FROM RECURRINGEXPENSEMANAGER WHERE RM_ID = :rm_id";
		public static const EXPENSE_ADD_RECORD:String="INSERT INTO EXPENSEMANAGER (em_category,em_amount,em_payee,em_memo,em_accountid,em_expenseid,em_expensetype,em_transactiondate,em_exchangerate,em_istransferrecord,em_userid,em_instru)" + "VALUES (:category,:amount,:payee,:memo,:account,:expenseid,:type,:date,:exrate,:istransferrecord,:USERID,:instru)";
		public static const EXPENSE_CATEGORY:String="select round(em_amount,2) em_amount,* from expensemanager where em_category = :categoryid  and em_userid in (select userid from selecteduser)";
		public static const EXPENSE_GET_DATA_ALL:String="SELECT * FROM EXPENSEMANAGER ORDER BY em_transactiondate where em_userid in (select userid from selecteduser)";
		public static const EXPENSE_SUMMARY_DATA:String="select em_expensetype,em_category,em_amount,strftime('%Y',em_transactiondate) em_year, strftime('%m',em_transactiondate) em_month,strftime('%d',em_transactiondate) em_day from expensemanager where em_userid  in (select userid from selecteduser) order by em_category,em_year,em_month,em_day";
		public static const EXPENSE_SUMMARY_QUERY:String="select round(sum(em_amount),2) amount, am_accountname name from expensemanager,accountmanager  where am_accountid = em_accountid  and em_userid in (select userid from selecteduser) group by am_accountid";
		public static const EXPENSE_YEARS:String="select distinct strftime('%Y',em_transactiondate) em_year from expensemanager where em_userid  in (select userid from selecteduser) order by em_year";
		public static const SELECT_EXPENSE_PAYEE:String="select * from expensemanager where em_payee=:payeeid and em_userid  in (select userid from selecteduser)";
		public static var SELECT_FILTERED_EXPENSES_ALL:String="select round(em_amount,2) em_amount,* ,strftime('%Y',em_transactiondate) em_year, strftime('%m',em_transactiondate) em_month,strftime('%d',em_transactiondate) em_day from expensemanager,accountmanager,payeemanager,categorymanager,currencymanager where em_payee=pm_id and em_accountid=am_accountid and em_category=cm_categoryid and cu_id = am_currencyid and am_accounttype in (1,2,4,11) and em_userid in (select userid from selecteduser) order by em_transactiondate desc";
		public static const SELECT_RECURRING_EXPENSES_ALL:String="select * from recurringexpensemanager,accountmanager,categorymanager  where  rm_fromaccount=am_accountid and rm_category=cm_categoryid  and rm_userid in (select userid from selecteduser) order by rm_nextexecutiondate asc";
//		public static const UPDATE_TRANSFER_EXPENSE_RECORD : String = "UPDATE EXPENSEMANAGER SET em_category = :category,em_amount=:amount,em_payee=:payee,em_memo=:memo,em_accountid = :account,em_transactiondate=:date,em_exchangerate=:exrate WHERE em_expenseid=:expenseid and em_expensetype=:type";
		public static const SELECT_TRANSFER_ACCOUNTNAME:String="select em_accountid,am_accountname from expensemanager,accountmanager where em_accountid != :accountid and em_expenseid = :expenseid and em_accountid = am_accountid";
		public static const UPDATE_CATEGORY:String="UPDATE  categorymanager SET cm_categoryname = :cm_categoryname,cm_categorydesc = :cm_categorydesc, cm_expensetype=:cm_expensetype WHERE cm_categoryid = :cm_categoryid";
		public static const UPDATE_EXPENSE_RECORD:String="UPDATE EXPENSEMANAGER SET em_category = :category,em_amount=:amount,em_payee=:payee,em_memo=:memo,em_accountid = :account,em_expensetype=:type,em_transactiondate=:date,em_exchangerate=:exrate WHERE em_expenseid=:expenseid";
		public static const UPDATE_EXPENSE_RECORD_FIELD:String="UPDATE EXPENSEMANAGER SET :FIELDNAME = :VALUE WHERE em_expenseid=:expenseid";
		public static const UPDATE_PAYEE:String="UPDATE  payeemanager SET pm_payeeName = :pm_payeeName WHERE pm_id = :pm_id";
		public static const UPDATE_RECURRING_EXPENSE_NEXT_EXECUTION_DATE:String="update recurringexpensemanager  set rm_nextexecutiondate = :rm_nextexecutiondate, rm_last_executed_date=:rm_last_executed_date where rm_id =:rm_id";
		public static const UPDATE_RECURSSIVE_EXPENSE:String="update recurringexpensemanager set rm_recursiondate=:rm_recursiondate  ,rm_amount=:rm_amount, rm_fromaccount=:rm_fromaccount ,rm_toaccount=:rm_toaccount ,rm_fromdate=:rm_fromdate ,rm_todate=:rm_todate ,rm_type =:rm_type" + ", rm_category=:rm_category ,rm_memo=:rm_memo,rm_status=:rm_status,rm_nextexecutiondate=:rm_nextexecutiondate,rm_last_executed_date=:rm_fromdate where rm_id=:rm_id  and rm_userid=" + AdminDataCache.ACCOUNT_IN_USE;
	}
}